static void exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(1);
}

//Соединение:
void CreateConn(char *conninfo)
{
	// strcpy(conninfo, "hostaddr= port= dbname= user= password=");
	conn = PQconnectdb(conninfo);
	if (PQstatus(conn) != CONNECTION_OK)
	{
		fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn));
		fprintf(stderr, "%s", PQerrorMessage(conn));
		exit_nicely(conn);
	}
}
//Поиск товара по номеру (штрих-коду):
bool GetItemByNum(PGconn *conn, char *tnum)
{
	unsigned char query[256];
	PGresult *res;

	// construct the query according to user's entered item
	strcpy(query, "SELECT partnumber, description, sellprice, id FROM parts ");
	strcat(query, "WHERE (upper(partnumber) = '") + tnum + "') ";

	// get rows from table
	res = PQexec(conn, query);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		PQclear(res);
		return false;
	}

	// next, add the rows to listctrl
	if (PQntuples(res) == 1)
	{
		PQgetvalue(res, 0, 0);		// partnumber?
		PQgetvalue(res, 0, 1);		// description?
		PQgetvalue(res, 0, 2);		// sellprice?
		PQgetvalue(res, 0, 3);		// id?
	}
	else
	{
		return false;
	}
	PQclear(res);
	return true;
}

//--------------------------------------------------------------------------------------------
bool listctrlItem::AddToList(wxString& ScannedItem, wxString& Quantity, wxString& Discount)
{
    // contruct the query according to user's entered item
    wxString strqry;
    strqry = wxT("SELECT partnumber, description, sellprice, id FROM parts ");
    strqry += wxT("WHERE (upper(partnumber) = '") + ScannedItem.Upper() + wxT("') ");
    
    // get rows from table
    PGresult *res;
    res = PQexec( wxGetApp().conn, strqry.char_str() );
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        //LastError.Printf(_("Unable to get record from table: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    // next, add the rows to listctrl
    wxString buf;
    long bufdata = 0;
    if ( PQntuples(res) == 1 )
    {
        buf.Empty();
        buf = wxT(PQgetvalue(res, 0, 0));
        InsertItem(0, buf);
        
        buf.Empty();
        buf = wxT(PQgetvalue(res, 0, 1));
        SetItem(0, 1, buf);
        
        SetItem(0, 2, Quantity);
        
        buf.Empty();
        buf = wxT(PQgetvalue(res, 0, 2));
        SetItem(0, 3, buf);
        
        SetItem(0, 4, Discount);
        
        double qty, disc, price, linetotal;
        Quantity.ToDouble(&qty);
        Discount.ToDouble(&disc); disc = disc / 100; disc = 1 - disc;
        buf.ToDouble(&price);
        linetotal = price * disc * qty;
        
        buf.Empty();
        buf << linetotal;
        SetItem(0, 5, buf);
        
        buf.Empty();
        buf = wxT(PQgetvalue(res, 0, 3));
        buf.ToLong(&bufdata);
        SetItemData(0, bufdata);

    } else {
        //wxMessageBox(_("Item not found!"));
        return false;
    };
    
    // clear the result and others to avoid memory leak
    PQclear(res);
    buf.Clear();
    strqry.Clear();
    
    return true;
}
//---------------------------------------------------------------------------------------------------------------
// Сохранение покупки:
bool SaveOrder(stDate date, bool IsCashPayment, listItem* ItemList)
{
	PGresult *res;
	char newID[256];
	char query[256];
	double GrandTotal = GetGrandTotal(ItemList);

	// начинаем транзакцию
	res = PQexec(conn, "BEGIN");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Unable to start a transaction: %s\n", PQerrorMessage(conn));
		PQclear(res);
		return false;
	}

	PQclear(res);
    
	// get the ID sequence latest value
	res = PQexec(conn, "SELECT nextval('id'::regclass)");
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "Unable to get a sequence number from database: %s\n", PQerrorMessage(conn));
		PQclear(res);
		return false;
	} else {
		newID = PQgetvalue(res, 0, 0);
		PQclear(res);
	};
    
	// save to table "ar"
	memset(query,0,sizeof(query));
	strcpy(query, "INSERT INTO ar (id, invnumber, transdate, customer_id, taxincluded, amount, ");
	strcat(query, "netamount, paid, datepaid, duedate, invoice, curr, employee_id, till, cashdiscount, ");
	strcat(query, "discountterms, warehouse_id, exchangerate, bank_id, paymentmethod_id) VALUES (");
	strcat(query, "'"); strcat(query, newID); strcat(query, "', ");			//id
	strcat(query, "'CASH', ");							//invnumber
	strcat(query, "'"); strcat(query, date); strcat(query, "',");			// transdate
	strcat(query, "'"); strcat(query, DefaultCustomer); strcat(query, "', ");	// customer_id выяснить откуда берётся DefaultCustomer
	strcat(query, "'FALSE', ");							// taxincluded
	strcat(query, DoubleToText(GrandTotal)); strcat(query, ", ");			// amount
	strcat(query, DoubleToText(GrandTotal)); strcat(query, ", ");			// netamount
	strcat(query, DoubleToText(GrandTotal)); strcat(query, ", ");			// paid
	strcat(query, "'"); strcat(query, date); strcat(query, "',");			// datepaid
	strcat(query, "'"); strcat(query, date); strcat(query, "',");			// duedate
	strcat(query, "'TRUE', ");							// invoice
	strcat(query, "'RUB', ");							// curr
	strcat(query, "10060, ");							// employee_id
	strcat(query, "'1', ");								// till
	strcat(query, "0, ");								// cashdiscount
	strcat(query, "0, ");								// discountterms
	strcat(query, "0, ");								// warehouse_id
	strcat(query, "1, ");								// exchangerate
	strcat(query, CashAccount);							// bank_id выяснить откуда берётся CashAccount
	strcat(query, ", ");
	strcat(query, "0)");								// paymentmethod_id
	res = PQexec(conn, query);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Unable to save to database: %s\n", PQerrorMessage(conn));
		PQclear(res);
		return false;
	};

	PQclear(res);
    
	// для каждой покупки в чеке

	// get from the PARTS table the oid, description, sellprice, income_accno_id, unit, assembly
	long oid;
	char description[256], sellprice[256], quantity[256], discount[256], linetotal[256];
	char income_acc_id[256], unit[256], assembly[256];
	char newInvID[256];
    
	//get the number of items from the current list
	int totalitems = ItemList->GetItemCount();
}


bool CashierData::SaveOrder(wxDateTime TransDate, bool IsCashPayment, listctrlItem* ItemList)
{
    PGresult *res;
    double GrandTotal = ItemList->GetGrandTotal();
    wxString strqry;
    wxString newID;
    
    // start a transaction block
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to start a transaction: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    PQclear(res);
    
    // get the ID sequence latest value
    strqry = wxT("SELECT nextval('id'::regclass)");
    res = PQexec(conn, strqry.char_str());
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        LastError.Printf(_("Unable to get a sequence number from database: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    } else {
        newID = PQgetvalue(res, 0, 0);
        PQclear(res);
    };
    
    // save to table "ar"
    strqry.Empty();
    strqry = wxT("INSERT INTO ar (id, invnumber, transdate, customer_id, taxincluded, amount, ");
    strqry += wxT("netamount, paid, datepaid, duedate, invoice, curr, employee_id, till, cashdiscount, ");
    strqry += wxT("discountterms, warehouse_id, exchangerate, bank_id, paymentmethod_id) VALUES (");
    strqry += wxT("'") + newID + wxT("', ");				//id
    strqry += wxT("'CASH', ");						//invnumber
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");		// transdate
    strqry += wxT("'") + DefaultCustomer + wxT("', ");			// customer_id
    strqry += wxT("'FALSE', ");						// taxincluded
    strqry << GrandTotal;						// amount
    strqry += wxT(", ");
    strqry << GrandTotal;						// netamount
    strqry += wxT(", ");
    strqry << GrandTotal;						// paid
    strqry += wxT(", ");
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");		// datepaid
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");		// duedate
    strqry += wxT("'TRUE', ");						// invoice
    strqry += wxT("'IDR', ");						// curr
    strqry += wxT("10060, ");						// employee_id
    strqry += wxT("'1', ");						// till
    strqry += wxT("0, ");						// cashdiscount
    strqry += wxT("0, ");						// discountterms
    strqry += wxT("0, ");						// warehouse_id
    strqry += wxT("1, ");						// exchangerate
    strqry << CashAccount;						// bank_id
    strqry += wxT(", ");
    strqry += wxT("0)");						// paymentmethod_id
    res = PQexec(conn, strqry.char_str());
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to save to database: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    };
    
    PQclear(res);
    
    // for each item in the list
    
    // get from the PARTS table the oid, description, sellprice, income_accno_id, unit, assembly
    long oid;
    wxString description, sellprice, quantity, discount, linetotal;
    wxString income_acc_id, unit, assembly;
    wxString newInvID;
    
    //get the number of items from the current list
    int totalitems = ItemList->GetItemCount();
    
    for (long i = 0; i <= totalitems-1; i++) {
        wxListItem desc, qty, price, disc, lnttl;
        
        // get oid
        oid = ItemList->GetItemData(i);
        // get description
        desc.m_mask = wxLIST_MASK_TEXT;
        desc.m_itemId = i;
        desc.m_col = 1;
        ItemList->GetItem(desc);
        description = desc.GetText();
        // get item quantity
        qty.m_mask = wxLIST_MASK_TEXT;
        qty.m_itemId = i;
        qty.m_col = 2;
        ItemList->GetItem(qty);
        quantity = qty.GetText();
        // get item price
        price.m_mask = wxLIST_MASK_TEXT;
        price.m_itemId = i;
        price.m_col = 3;
        ItemList->GetItem(price);
        sellprice = price.GetText();
        // get item discount
        disc.m_mask = wxLIST_MASK_TEXT;
        disc.m_itemId = i;
        disc.m_col = 4;
        ItemList->GetItem(disc);
        discount = disc.GetText();
            // convert to the real discount value
            double temp;
            discount.ToDouble(&temp); temp = temp / 100;
            discount.Empty();
            discount << temp;
            
        // get item price
        lnttl.m_mask = wxLIST_MASK_TEXT;
        lnttl.m_itemId = i;
        lnttl.m_col = 5;
        ItemList->GetItem(lnttl);
        linetotal = lnttl.GetText();
        
        // get the remaining value from PARTS table
        strqry.Empty();
        strqry = wxT("SELECT income_accno_id, unit, assembly FROM parts WHERE id = ");
        strqry << oid;
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            LastError.Printf(_("Unable to get data from parts table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        };
        if ( PQntuples(res) == 1 )
        {
            income_acc_id = wxT(PQgetvalue(res, 0, 0));
            unit = wxT(PQgetvalue(res, 0, 1));
            assembly = wxT(PQgetvalue(res, 0, 2));
        };
        
        PQclear(res);
        
        // UPDATING invoice TABLE
        /// get the INVOICEID sequence latest value
        strqry.Empty();
        strqry = wxT("SELECT nextval('invoiceid'::regclass)");
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            LastError.Printf(_("Unable to get a sequence number from database: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        } else {
            newInvID = PQgetvalue(res, 0, 0);
            PQclear(res);
        };
        
        /// save to INVOICE table 
        strqry.Empty();
        strqry = wxT("INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated, ");
        strqry += wxT("sellprice, fxsellprice, discount, assemblyitem, unit, lineitemdetail) VALUES (");
        strqry += newInvID + wxT(", ");				// id
        strqry += newID + wxT(", ");				// trans_id
        strqry << oid;						// parts_id
        strqry += wxT(", ");
        strqry += wxT("'") + description + wxT("', ");		// description
        strqry += wxT("'") + quantity + wxT("', ");		// qty
        strqry += wxT("0, ");					// allocated
        strqry += linetotal + wxT(", ");			// sellprice
        strqry += sellprice + wxT(", ");			// fxsellprice
        strqry += discount + wxT(", ");				// discount
        strqry += wxT("'") + assembly + wxT("', ");		// assemblyitem
        strqry += wxT("'") + unit + wxT("', ");			// unit
        strqry += wxT("FALSE)");				// lineitemdetail
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to invoice table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        /// save to ACC_TRANS table
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, id) VALUES (");
        strqry += newID + wxT(", ");
        strqry += income_acc_id + wxT(", ");
        strqry += linetotal + wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
        strqry += newInvID + wxT(")");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        /// update the ONHAND field of the PARTS table
        /// substract the quantity with the existing quantity
        strqry.Empty();
        strqry = wxT("UPDATE parts SET onhand = onhand - ");
        strqry += quantity;
        strqry += wxT(" WHERE id = ");
        strqry << oid;
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to update PARTS table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
    };
    
    /// save to ACC_TRANS table
    strqry.Empty();
    strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (");
    strqry += newID + wxT(", ");
    strqry += ARaccount + wxT(", ");
    strqry << GrandTotal * -1;
    strqry += wxT(", ");
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("')");
    
    res = PQexec(conn, strqry.char_str());
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    PQclear(res);
    
    /// save the payment to the ACC_TRANS table
    if(IsCashPayment) {
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (");
        strqry += newID + wxT(", ");
        strqry += ARaccount + wxT(", ");
        strqry << GrandTotal;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("')");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, id) VALUES (");
        strqry += newID + wxT(", ");
        strqry += CashAccount + wxT(", ");
        strqry << GrandTotal * -1;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
        strqry += wxT("1)");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
    } else {
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (");
        strqry += newID + wxT(", ");
        strqry += ARaccount + wxT(", ");
        strqry << GrandTotal;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("')");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, id) VALUES (");
        strqry += newID + wxT(", ");
        strqry += CardAccount + wxT(", ");
        strqry << GrandTotal * -1;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
        strqry += wxT("1)");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
    };
    
    // end the transaction
    res = PQexec(conn, "END");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to end a transaction: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    PQclear(res);
    return true;
};

